"""
Case Type   : 功能测试
Case Name   : 自治事务函数不支持返回非out形式的record类型,部分测试点合理报错
Create At   : 2024/7/3
Owner       : peilinqian
Description :
    step1: 创建表;
    step2: 创建包含自治事务的函数,返回record类型的参数;
    step3: 创建不包含自治事务的函数,返回record类型的参数;
Expect      :
    step1: 创建表; expect：创建成功
    step2: 创建包含自治事务的函数,返回record类型的参数;expect:创建成功，但是调用失败
    step3: 创建不包含自治事务的函数,返回record类型的参数;expect:创建成功，调用成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.com = Common()
        self.constant = Constant()
        self.t_name = 't_autonomous_transaction_0040'
        self.func_name = 'f_autonomous_transaction_0040'

    def test_main(self):
        step_txt = '----step1: 创建表; expect：创建成功----'
        self.log.info(step_txt)
        sql = f"drop table if exists {self.t_name};" \
              f"create table {self.t_name}(a int,b int);"
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, msg,
                      '执行失败:' + step_txt)

        step_txt = '----step2: 创建包含自治事务的函数,返回record类型的参数;' \
                   'expect:创建成功，但是调用失败----; '
        self.log.info(step_txt)
        self.log.info('----创建包含自治事务的函数,返回record类型的参数----')
        sql = f'''drop function {self.func_name}();
        CREATE OR REPLACE FUNCTION {self.func_name}()
        RETURNS RECORD AS \$\$
        DECLARE
            pragma autonomous_transaction;
            result_record RECORD;
        BEGIN
            insert into {self.t_name} values(1,1),(2,2);
            SELECT a,b INTO result_record FROM {self.t_name} WHERE a =2;
            RETURN result_record;
        END;
        \$\$ LANGUAGE plpgsql;
        '''
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.log.info('----调用包含自治事务的函数,返回record类型的参数----')
        sql = f'delete from {self.t_name};select {self.func_name}()'
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        err_flag = 'ERROR:  unrecognized return type for PLSQL function'
        self.assertIn(err_flag, msg, '执行失败:' + step_txt)

        step_txt = '----step3: 创建不包含自治事务的函数,返回record类型的参数;' \
                   'expect:创建成功，调用成功----; '
        self.log.info(step_txt)
        self.log.info('----创建包含自治事务的函数,返回record类型的参数----')
        sql = f'''drop function {self.func_name}();
        CREATE OR REPLACE FUNCTION {self.func_name}()
        RETURNS RECORD AS \$\$
        DECLARE
            result_record RECORD;
        BEGIN
            insert into {self.t_name} values(1,1),(2,2);
            SELECT a,b INTO result_record FROM {self.t_name} WHERE a =2;
            RETURN result_record;
        END;
        \$\$ LANGUAGE plpgsql;
        '''
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.log.info('----调用包含自治事务的函数,返回record类型的参数----')
        sql = f'delete from {self.t_name};select {self.func_name}()'
        msg = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg)
        self.assertIn('(2,2)', msg, '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step_txt = '----删除表，恢复参数; expect：成功----'
        self.log.info(step_txt)
        sql = f"drop table if exists {self.t_name};"
        msg1 = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg1)
        sql = f"drop function {self.func_name}();"
        msg2 = self.pri_sh.execut_db_sql(sql)
        self.log.info(msg2)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, msg1,
                      '执行失败:' + step_txt)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, msg2,
                      '执行失败:' + step_txt)